<?php

/**
 * @author  spring
 * @version 2018-01-03
 */
class Task_inventories extends Minion_Task
{

    //php /var/www/index.php --task=inventories
    protected function _execute(array $params)
    {
        set_time_limit(0);
        ini_set('memory_limit','500M');
        $thismonth = date('Y-m-01');
        $lastmonth = date('Y-m-01',strtotime("-1 month"));
        $instance=Database::instance('newerp');
        $instance->begin();
        try{
            //清除非正常入库当期单价
            $delete =DB::query(Database::DELETE,"DELETE FROM warehouseorders_single_price WHERE warehouseorders_date='$thismonth'")->execute('newerp');
            //写入非正常入库当期(月初到月末)单价 逻辑：历史正常入库取正常入库单价，如果没有正常入库单价取期初单价，如果期初单价也没有取sku基础资料成本价，如果都没有默认0
            $sql = "INSERT INTO warehouseorders_single_price(sku,single_price,warehouseorders_date)
                SELECT t.sku,(
                    CASE WHEN t.single_price IS NULL
                    THEN (SELECT max(num) FROM skusystem_price WHERE sku=t.sku)
                    ELSE t.single_price
                    END
                ) single_price,'$thismonth' 
                FROM 
                (
                    SELECT war.`sku` `sku`, 
                    (
                        CASE  WHEN war2.`single_price` is null THEN (
                            SELECT SUM(`cost`) / SUM(`quantity`) `single_price`
                            FROM `inventories` WHERE `inventory_date`='$lastmonth' AND sku=war.sku 
                        )
                        ELSE war2.`single_price` 
                        END 
                    ) single_price
                    FROM `warehouseorders` war
                    LEFT JOIN (
                        SELECT `sku`,`cost`/`warehouse_quantity` `single_price`,max(warehouse_date) FROM `warehouseorders`
                        WHERE `type`=10 AND `warehouse_date`<'$thismonth' GROUP BY sku
                    )  war2 ON war2.sku=war.sku
                    WHERE war.`warehouse_date` >='$lastmonth' AND war.`warehouse_date`<'$thismonth' AND war.`type` != 10 GROUP BY war.sku 
                ) t ";
            $insertSinglePrice = DB::query(Database::INSERT,$sql)->execute('newerp');
            //更新非正常入库成本价
            $sql = "UPDATE `warehouseorders` war,warehouseorders_single_price warsp SET war.`cost`= warsp.single_price*war.warehouse_quantity
                WHERE war.`type`!=10 AND war.`warehouse_date`>='$lastmonth' AND war.`warehouse_date`<'$thismonth' 
                AND warsp.`warehouseorders_date`='$thismonth' AND warsp.`sku`=war.`sku`";
            $updateSinglePrice = DB::query(Database::UPDATE,$sql)->execute('newerp');
            //清除已有当期加权平均价
            $delete =DB::query(Database::DELETE,"DELETE FROM inventories_cost WHERE inventory_date='$thismonth'")->execute('newerp');
            $sql = "INSERT INTO inventories_cost(sku,price,inventory_date)
            SELECT a.`sku` AS `sku`,SUM(a.`cost`)/ SUM(a.`quantity`) AS `single_price`,'$thismonth' inventory_date FROM
            (
                SELECT `sku`,`quantity`,`cost` FROM `inventories` WHERE `inventory_date`='$lastmonth'
                UNION ALL
                SELECT `sku`,`warehouse_quantity` `quantity`,`cost` FROM `warehouseorders`
                WHERE `warehouse_date`>='$lastmonth' AND `warehouse_date`<'$thismonth'
            ) a
            GROUP BY `sku`";
            //写入当期加权平均价
            $insertPrice = DB::query(Database::INSERT,$sql)->execute('newerp');
            //更新出库单sku成本价
            $sql = "UPDATE deliveryorders  de,inventories_cost ic SET de.cost = ic.price*de.quantity
                WHERE de.delivery_date >='$lastmonth' AND de.delivery_date <'$thismonth'  AND ic.inventory_date='$thismonth'
                AND de.sku=ic.sku AND de.cost <> ic.price*de.quantity";
            $updatePrice = DB::query(Database::UPDATE,$sql)->execute('newerp');
            //清除当期期初成本
            $delete =DB::query(Database::DELETE,"DELETE FROM inventories WHERE inventory_date = '$thismonth'")->execute('newerp');
            //写入当期期初库存
            $sql = "INSERT INTO inventories (sku,quantity,warehouseid,inventory_date,cost)
                SELECT a.`sku` `sku`,SUM(a.`quantity`) `quantity`,warehouseid,'$thismonth' inventory_date,SUM(a.`quantity`)*cost.price cost
                FROM
                (
                    SELECT `sku`, `quantity` `quantity`,warehouseid FROM `inventories`
                    WHERE  `inventory_date`='$lastmonth' AND `quantity`!=0
                    UNION ALL
                    SELECT `sku`, `warehouse_quantity` `quantity`,warehouseid FROM `warehouseorders`
                    WHERE `warehouse_date`>='$lastmonth' AND `warehouse_date`<'$thismonth'
                    UNION ALL
                    SELECT `sku`, -`quantity` `quantity`,warehouseid FROM `deliveryorders`
                    WHERE `delivery_date`>='$lastmonth' AND `delivery_date`<'$thismonth'
                ) a,inventories_cost cost
                WHERE cost.sku=a.sku AND cost.inventory_date='$thismonth'
                GROUP BY a.`sku`,a.warehouseid HAVING quantity>0";
            $insertInventories = DB::query(Database::INSERT,$sql)->execute('newerp');
            $instance->commit();
        }catch(Database_Exception $e){
            $instance->rollback();
            $this->endTask($e->getMessage());
        }
        $this->endTask('Success');
    }

    // 计划任务结束输出
    protected function endTask($msg, $code = 0, $data = null) {
        $code = intval($code);
        if(!is_null($data)) {
            $data = is_array($data) ? json_encode($data) : $data;
            $dataStr = ",\n\t\"data\": \"" . addslashes($data) . "\"";
        }
        echo "{\n\t\"code\": $code,\n\t\"info\": \"$msg\"{$dataStr}\n}\n";
        exit;
    }
}
